VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsSearch"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Dim obj_ADOGetResponseDataProc As ADODB.Command
Dim obj_ADODirListRequests As ADODB.Command
Dim obj_ADODirGetDirectiveDetailsProc As ADODB.Command
Dim obj_ADOMartailSearchDataProc As ADODB.Command
Dim obj_ADOGetFundDetailsProc As ADODB.Command
Dim obj_ADOGetEmployerIdDetails As ADODB.Command
Dim obj_ADOGetDirectiveDetails As ADODB.Command

'recordsets
Dim rsDirectiveData As ADODB.Recordset
Dim rsGetResponseData As ADODB.Recordset
Dim rsRequestList As ADODB.Recordset
Dim rsComID As ADODB.Recordset
Dim rsFundDetails As ADODB.Recordset
Dim ADOEnums As ADODB.Recordset
Dim rsEmp As ADODB.Recordset
Dim rsDirDet As ADODB.Recordset
Dim rsModUser As ADODB.Recordset
Dim rsGetUserList As ADODB.Recordset
Dim rsHasRights As ADODB.Recordset

Option Explicit

Public Function PerformSearch(ByVal strSearchBy As String, _
                       ByVal strSearchFor As String, _
                       ByVal strNextPre As String, _
                       ByRef strErrMSG As Variant, _
                       ByRef iCurrentPage As Variant, _
                       ByVal strUsername As String) As String
                       
    On Error GoTo PROC_ERROR
    
    'this sub will perform the search by calling the relevant stored procs in a loop
    'and creates the built table in HTML with everything in it.
    Dim strPAYEHolder As String
    Dim strDetHolder As String
    Dim strHTMLHolder As String
    Dim strSQL As String
    Dim strIDHolder As String
    Dim strWhere As String
    Dim strFundNoHolder As String
    Dim strIAm As String
    Dim strIAmId As String
    Dim strIWantTO As String
    Dim strCanYN As String
    Dim strCslev As String
    Dim sHolderAnt As String
    Dim directive_number As String
    Dim Taxpayer As String
    Dim birthdate As String
    Dim req_on As String
    Dim req_by As String
    Dim meaning As String
    Dim sReqSeqNum As String
    Dim strStatus As String
    
    strErrMSG = ""
    'check the recordset to see if its empty!
    If rsRequestList.BOF = True And rsRequestList.EOF = True Then
        'generate the HTML to hold the returned info
        
        strHTMLHolder = "" & _
            "<tr>" & _
                "<td class=FormLeftCell valign=top>No normal information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
            "</tr>"
                
        iCurrentPage = -1
        'the recordset is empty!
        PerformSearch = strHTMLHolder
        Exit Function
    End If
    'disallow the browseing past the beginning or the end of the set, lower than 1 or
    'greater than page count
        
    rsRequestList.AbsolutePage = CLng(iCurrentPage)
    
    'get the first id in the recordset
'    strFirstIDHolder = rsRequestList("req_seq_num")
    
    'loop to the end of the page
    Do While rsRequestList.EOF = False And rsRequestList.AbsolutePage = iCurrentPage
    
        'set the cursor to the client so we can disconnect the rs
        rsGetResponseData.CursorLocation = adUseClient
        strSQL = "exec dir_get_response_data " & rsRequestList("req_seq_num")
        
        'get the data
        rsGetResponseData.Open strSQL, obj_ADOConn
        'disconnect the recordset and free up the connection for another
        'resultset
        Set obj_ADOGetResponseDataProc.ActiveConnection = Nothing
        If CStr(rsRequestList("meaning")) <> "IRP3B" And CStr(rsRequestList("meaning")) <> "IRP3A" And CStr(rsRequestList("meaning")) <> "IRP3C" Then
            strWhere = "dir_get_fund_id_details "
            strSQL = "exec dir_get_fund_id_details " & rsRequestList("fun_id")
            rsFundDetails.Open strSQL, obj_ADOConn
              If rsFundDetails.BOF = True And rsFundDetails.EOF = True Then
                'there is no fund number and it must be set to n/a
                strFundNoHolder = "n/a"
            Else
                strFundNoHolder = IIf(IsNull(rsFundDetails("fund_number")), "", CStr(rsFundDetails("fund_number")))
            End If
        Else
            strFundNoHolder = "n/a"
        End If
        
        If IsNull(rsRequestList("emp_id")) = True Then
            strIDHolder = "0"
        Else
            strIDHolder = rsRequestList("emp_id")
        End If
        
        strWhere = "dir_get_employer_id_details "
        'call the get emp details proc
        strSQL = "exec dir_get_employer_id_details " & strIDHolder
        rsEmp.Open strSQL, obj_ADOConn
              
        If IsNumeric(rsGetResponseData("directive_number")) = True Then
            strSQL = "exec dir_get_directive_details " & rsGetResponseData("directive_number")
        Else
            strSQL = "exec dir_get_directive_details 0"
        End If
        rsDirDet.Open strSQL, obj_ADOConn
                
        'validate the sars application number
        If rsDirDet.EOF = True And rsDirDet.BOF = True Then
            'recordset empty
            strDetHolder = "n/a"
        Else
            If IsNull(rsDirDet("sars_appl_id")) = True Then
                strDetHolder = "n/a"
            Else
                strDetHolder = rsDirDet("sars_appl_id")
            End If
        End If
    
        'check the paye_ref_no (it will not exist in many cases.)
        If rsEmp.BOF = True And rsEmp.EOF = True Then
            'the recordset is empty!
            strPAYEHolder = "n/a"
        Else
            If IsNull(rsEmp("paye_ref_no")) = True Then
                strPAYEHolder = "n/a"
            Else
                strPAYEHolder = ValidatePAYERefNo(rsEmp("paye_ref_no"))
            End If
        End If
        'load the users access rights for the anchor
        
        'Get the use_id for the user that logged in
        strSQL = "select use_id from dir_users where username = '" & strUsername & "'"
        rsHasRights.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
        strIAm = rsHasRights("use_id")
        rsHasRights.Close
        
        'Get the use_id for the user that requested the directive - Liberty Users
        strSQL = "select use_id from dir_contact_details where contact_name ='" & CStr(rsRequestList("req_by")) & "'"
        rsHasRights.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
        strIWantTO = rsHasRights("use_id")
        rsHasRights.Close
        
        'Check if the user that logged in has access to see this request details
        'a) If user that logged in is supervisor
        'b) Defaults for System and Admin user
        strSQL = "exec dir_check_load_user " & strIAm & "," & strIWantTO
        rsHasRights.Open strSQL, obj_ADOConn
        strCanYN = rsHasRights("see_user")
        rsHasRights.Close
        
        'Check if the request was made by the same user that logged in
        If strIAm = strIWantTO Then
            strCanYN = "Y"
        End If
        
        'Shane: New code added 2003/01/07
        'Code added to check if the user that logged in, is from the Business Unit INDV (credit card users)
        'If so, only show the user his own request else show all requests in Business Unit (eg for Liberty users
        'Get the use_id for the user that requested the directive
        'New code starts
        strSQL = "select cslev_id from dir_users where username = '" & strUsername & "'"
        rsHasRights.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
        strCslev = rsHasRights("cslev_id")
        rsHasRights.Close
        If strCslev = "9" Then
            'Get the use_id for the user that requested the directive - Credit Card users
            strSQL = "select use_id from dir_request where req_seq_num ='" & CStr(rsRequestList("req_seq_num")) & "'"
            rsHasRights.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
            strIWantTO = rsHasRights("use_id")
            rsHasRights.Close
            If strIAm = strIWantTO Then
                strCanYN = "Y"
            End If
            If UCase(strCanYN) = "Y" Then
                'Build HTML
                strHTMLHolder = "<tr><td class=FormLeftCell valign=top style=""width:6%""><A href=""../RequestInfo/Req_Info.asp?nReqSeqNum=" & CStr(rsRequestList("req_seq_num")) & "&sFormType=" & rsRequestList("scod_form_type") & """>" & CStr(rsRequestList("req_seq_num")) & "</A></td> "
                iCurrentPage = -1
                'the recordset is empty!
                PerformSearch = strHTMLHolder
           Else
                strHTMLHolder = "" & _
                "<tr>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "</tr>"
                iCurrentPage = -1
                'the recordset is empty!
                PerformSearch = strHTMLHolder
                Exit Function
            End If
        'new code ends
        Else
            strHTMLHolder = strHTMLHolder & "" & _
                    "<tr>"
                    'Tina: 2004/02/25... can't click on req num when status = 'CR'
                strSQL = "select scod_process_status from dir_request where req_seq_num = '" & rsRequestList("req_seq_num") & "'"
                rsHasRights.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
                strStatus = rsHasRights("scod_process_status")
                rsHasRights.Close
                If UCase(strCanYN) = "Y" And UCase(strStatus) <> "CR" Then
                    strHTMLHolder = strHTMLHolder & "" & _
                        "<td class=FormLeftCell valign=top style=""width:6%""><A href=""../RequestInfo/Req_Info.asp?nReqSeqNum=" & CStr(rsRequestList("req_seq_num")) & "&sFormType=" & rsRequestList("scod_form_type") & """>" & CStr(rsRequestList("req_seq_num")) & "</A></td> "
                Else
                    strHTMLHolder = strHTMLHolder & "" & _
                        "<td class=FormLeftCell valign=top style=""width:6%"">" & IIf(IsNull(rsRequestList("req_seq_num")), "", CStr(rsRequestList("req_seq_num"))) & "</td> "
                End If
        End If
        
        'generate the rest of the HTML to hold the returned info
        
        Dim iCounter As Integer
        
        
        'The following 9 "If" statements added by Shane on 30/01/2002
        'make sure that all of the values used to build up the display, are not NULL
        'SARS Fund Number
        If IsNull(strFundNoHolder) = True Then
            strFundNoHolder = "n/a"
        Else
            strFundNoHolder = CStr(strFundNoHolder)
        End If
        'Employer PAYE number
        If IsNull(strPAYEHolder) = True Then
            strPAYEHolder = "n/a"
        Else
            strPAYEHolder = CStr(strPAYEHolder)
        End If
        'Directive Number
        If IsNull(rsGetResponseData("directive_number")) = True Then
            directive_number = "n/a"
        Else
            directive_number = CStr(rsGetResponseData("directive_number"))
        End If
        'SARS application No
        If IsNull(strDetHolder) = True Then
            strDetHolder = "n/a"
        Else
            strDetHolder = CStr(strDetHolder)
        End If
        'Taxpayer
        If IsNull(rsRequestList("Taxpayer")) = True Then
            Taxpayer = "n/a"
        Else
            Taxpayer = CStr(rsRequestList("Taxpayer"))
        End If
        'Taxpayers Date of birth
        If IsNull(rsRequestList("birthdate")) = True Then
            birthdate = "n/a"
        Else
            birthdate = Format(CStr(rsRequestList("birthdate")), "yyyymmdd")
        End If
        'Requested Date
        If IsNull(rsRequestList("req_on")) = True Then
            req_on = "n/a"
        Else
            req_on = Format(CStr(rsRequestList("req_on")), "yyyymmdd")
        End If
        'Requested By
        If IsNull(rsRequestList("req_by")) = True Then
            req_by = "n/a"
        Else
            req_by = CStr(rsRequestList("req_by"))
        End If
        'Form Type
        If IsNull(rsRequestList("meaning")) = True Then
            meaning = "n/a"
        Else
            meaning = CStr(rsRequestList("meaning"))
        End If
        'Shane: New code added 23/04/2003 to add new field to tracking
        ' -------------------------------------------------------- Get Request Status Code Meaning ------------------------------------
        Dim dllRequestStatus As Object
        Dim sReqStatus As String
        Dim nReqSeqNumber As String

        nReqSeqNumber = CStr(rsRequestList("req_seq_num"))
        Set dllRequestStatus = CreateObject("KeyTax.clsRequests")
        'Status
        sReqStatus = dllRequestStatus.GetRequestStatus(nReqSeqNumber)
        If IsNull(sReqStatus) = True Then
            sReqStatus = "n/a"
        Else
            sReqStatus = sReqStatus
        End If

        Set dllRequestStatus = Nothing

        ' -----------------------------------------------------------------------------------------------------------------------------
        
         iCounter = 1
        strHTMLHolder = strHTMLHolder & ""
        iCounter = 1
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:10%"">" & strFundNoHolder & "</td>"
        iCounter = 2
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:10%"">" & strPAYEHolder & "</td>"
        iCounter = 3
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:10%"">" & directive_number & "</td>"
        iCounter = 4
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:10%"">" & strDetHolder & "</td>"
        iCounter = 5
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:11%"">" & Taxpayer & "</td>"
        iCounter = 6
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:11%"">" & birthdate & "</td>"
        iCounter = 7
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:11%"">" & req_on & "</td>"
        iCounter = 8
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:11%"">" & req_by & "</td>"
        iCounter = 9
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:10%"">" & meaning & "</td>"
        iCounter = 10
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:10%"">" & sReqStatus & "</td>"
        iCounter = 11
        strHTMLHolder = strHTMLHolder & "</tr>"
        iCounter = 12
            '
        'close down the recordsets
         iCounter = 2
        If CStr(rsRequestList("meaning")) <> "IRP3B" And CStr(rsRequestList("meaning")) <> "IRP3A" And CStr(rsRequestList("meaning")) <> "IRP3C" Then
            rsFundDetails.Close
        End If
        iCounter = 3
        rsGetResponseData.Close
        rsDirDet.Close
        rsEmp.Close
        
        rsRequestList.MoveNext
    Loop
    
    PerformSearch = strHTMLHolder
    
    obj_ADOConn.Close
    Set obj_ADOConn = Nothing
   Exit Function
PROC_ERROR:
    App.LogEvent ("Null error for clsSearch" & Now & "Line number last reached_really!!:" & iCounter & "." & "String = " & strHTMLHolder)
    Err.Raise vbErrorNumbers.KT_PERFORMSEARCH, "Keytax.clsSearch.PerformSearch", Err.Description & "Null error for clsSearch" & Now & "Line number last reached:" & iCounter & "." & "String = " & strHTMLHolder & "Req by: " & CStr(rsRequestList("req_by"))
End Function
Private Function ValidatePAYERefNo(ByVal strPAYERefNumber As String) As String
    'this function gets the paye ref num and makes sure its not null ect
        If IsNull(strPAYERefNumber) = True Then
            'the PAYE no is NULL.
            ValidatePAYERefNo = "n/a"
        Else
            'everything is ok so assign the value
            ValidatePAYERefNo = strPAYERefNumber
        End If
End Function

Public Sub initSearch(ByVal strSearchBy As String, _
                            ByVal strSearchFor As String, _
                            ByVal strNextPre As String, _
                            ByVal strComId As String, _
                            ByRef iCurrentPage As Variant, _
                            ByRef iMaxPages As Variant, _
                            ByVal strUsername As String, _
                            Optional ByVal varRowsPerPage As Variant)
                            
    On Error GoTo PROC_ERROR
    
    Dim strSQL
    Dim ConnHolder As Object
    Dim strConnString As String
    
    Dim iMaxPagesHolder As Integer
    Dim iCurrentPageHolder As Integer
    Dim iRowsPerPage  As Integer
    
    'validate the rows per page variable from the web
    If CStr(varRowsPerPage) = "" Then
        iRowsPerPage = 20
    Else
        If CInt(varRowsPerPage) = 0 Then
            iRowsPerPage = 20
        Else
            If IsNumeric(varRowsPerPage) = True Then
                iRowsPerPage = CInt(varRowsPerPage)
            Else
                iRowsPerPage = 20
            End If
        End If
    End If
    
    'validate the user to make sure they have the correct access to view
    'lots of directives.
    
    'the user may only see directives that are in his name
    
    Call modGeneral.dbConnect
    
    Set rsRequestList = New ADODB.Recordset
    
    iCurrentPageHolder = CInt(iCurrentPage)
    iMaxPagesHolder = CInt(iMaxPagesHolder)
    
    'the cursor must support bookmarks for us to use the pageing functionallity
    rsRequestList.CursorType = adOpenStatic
    rsRequestList.CursorLocation = adUseClient
    rsRequestList.PageSize = iRowsPerPage
    rsRequestList.CacheSize = iRowsPerPage 'set the cached number of records = 20
    
    strSQL = "exec dir_list_requests '" & strComId & "','" & strSearchFor & "','" & strSearchBy & "'"
    rsRequestList.Open strSQL, obj_ADOConn, adOpenStatic, adLockBatchOptimistic
    
    If rsRequestList.EOF = True And rsRequestList.BOF = True Then
        'there were no records returned for the search, so return -1 for
        'the current page to let the web page know to display the "no results found" string
        iCurrentPage = -1
        Exit Sub
    Else
        iMaxPagesHolder = rsRequestList.PageCount
        'manage the current page var
        If iCurrentPageHolder < 1 Then
            iCurrentPageHolder = 1
        End If
    
        If iCurrentPageHolder > iMaxPagesHolder Then
            iCurrentPageHolder = iMaxPagesHolder
        End If
        
        'pass out the integers
        iCurrentPage = CStr(iCurrentPageHolder)
        iMaxPages = CStr(iMaxPagesHolder)
    End If
    
    'disconnect the recordset to free up resources
 '   rsRequestList.ActiveConnection = Nothing
    Exit Sub
PROC_ERROR:
        Err.Raise vbErrorNumbers.KT_INIT_SEARCH, "Keytax.clsSearch.initSearch", Err.Description
End Sub

Public Sub DestroyMe(ByVal strErrMSG As String)
    'this sub kills all the ado objects
    On Error GoTo PROC_ERROR
    
    Set obj_ADOGetResponseDataProc = Nothing
    Set obj_ADODirListRequests = Nothing
    Set obj_ADODirGetDirectiveDetailsProc = Nothing
    Set obj_ADOMartailSearchDataProc = Nothing
    Set obj_ADOGetFundDetailsProc = Nothing
    Set obj_ADOGetEmployerIdDetails = Nothing
    Set obj_ADOGetDirectiveDetails = Nothing

    'recordsets
    Set rsDirectiveData = Nothing
    Set rsGetResponseData = Nothing
    Set rsRequestList = Nothing
    Set rsComID = Nothing
    Set rsFundDetails = Nothing
    Set ADOEnums = Nothing
    Set rsEmp = Nothing
    Set rsDirDet = Nothing
    Set rsHasRights = Nothing
    
    Exit Sub
PROC_ERROR:
    strErrMSG = Err.Description & " - clsSearch.DestroyMe"
End Sub

Private Sub Class_Initialize()

    Set obj_ADOGetResponseDataProc = New ADODB.Command
    Set obj_ADODirListRequests = New ADODB.Command
    Set obj_ADODirGetDirectiveDetailsProc = New ADODB.Command
    Set obj_ADOMartailSearchDataProc = New ADODB.Command
    Set obj_ADOGetFundDetailsProc = New ADODB.Command
    Set obj_ADOGetEmployerIdDetails = New ADODB.Command
    Set obj_ADOGetDirectiveDetails = New ADODB.Command

    'recordsets
    Set rsDirectiveData = New ADODB.Recordset
    Set rsGetResponseData = New ADODB.Recordset
    Set rsRequestList = New ADODB.Recordset
    Set rsComID = New ADODB.Recordset
    Set rsFundDetails = New ADODB.Recordset
    Set ADOEnums = New ADODB.Recordset
    Set rsEmp = New ADODB.Recordset
    Set rsDirDet = New ADODB.Recordset
    Set rsGetUserList = New ADODB.Recordset
    Set rsModUser = New ADODB.Recordset
    Set rsHasRights = New ADODB.Recordset
    
End Sub

Private Function PerformUserSearchActual(ByVal strKeytaxUser As String, _
                                    ByVal strComId As String, _
                                    ByVal strSearchText As String, _
                                    ByVal strSearchBy As String, _
                                    ByRef varCurrentPage As Variant, _
                                    ByRef varMaxPages As Variant, _
                                    ByVal strNextPrev As String) As String
            
'this function will perform the user search and return the generated html

    Dim strSQL As String
    Dim sModUser As String
    Dim strHTMLHolder As String
    Dim strIAm As String
    Dim strIWantTO As String
    Dim strCanYN As String
    On Error GoTo PROC_ERROR
    
    'find out if this user has rights view the users that are returned in the list
    strSQL = "select use_id from dir_users where username = '" & strKeytaxUser & "'"
    rsHasRights.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
    strIAm = rsHasRights("use_id")

    'if the recordset is empty return the no users html
    If rsGetUserList.BOF = True And rsGetUserList.EOF = True Then
        strHTMLHolder = strHTMLHolder & " " & _
        "<tr> " & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
        "</tr>"
        PerformUserSearchActual = strHTMLHolder
        Exit Function
    End If
    
    'the main user loop to generate the table's html
    rsGetUserList.AbsolutePage = CInt(varCurrentPage)
    Do While rsGetUserList.EOF = False And rsGetUserList.AbsolutePage = CInt(varCurrentPage)
        
        'strHTMLHolder = strHTMLHolder & rsGetUserList("username") & vbCrLf
        
        'build the output html
        strHTMLHolder = strHTMLHolder & " " & _
        "<tr> "
        strSQL = "select use_id from dir_users where username = '" & rsGetUserList("username") & "'"
        rsHasRights.Close
        rsHasRights.Open strSQL, obj_ADOConn
        strIWantTO = rsHasRights("use_id")
        
        rsHasRights.Close
        strSQL = "exec dir_check_load_user " & strIAm & "," & strIWantTO
        rsHasRights.Open strSQL, obj_ADOConn
        strCanYN = rsHasRights("see_user")
        
        'build the anchor/no achor on the username depending on the calling
        'users rights.
        If UCase(strCanYN) = "Y" Then
            strHTMLHolder = strHTMLHolder & " " & _
            "<td class=""NestedTableCell"" valign=top width=""""    ><a href=""UserDetails.asp?strUserName=" & rsGetUserList("username") & "&strReferrer=LIST_USER""><font size=1>" & rsGetUserList("username") & "</font></a></td>"
        Else
            If strIWantTO = strIAm Then
                strHTMLHolder = strHTMLHolder & " " & _
                "<td class=""NestedTableCell"" valign=top width=""""    ><a href=""UserDetails.asp?strUserName=" & rsGetUserList("username") & "&strReferrer=LIST_USER""><font size=1>" & rsGetUserList("username") & "</font></a></td>"
            Else
                strHTMLHolder = strHTMLHolder & " " & _
                "<td class=""NestedTableCell"" valign=top width=""""    ><font size=1>" & rsGetUserList("username") & "</font></td>"
            End If
        End If
        strHTMLHolder = strHTMLHolder & " " & _
            "<td class=""NestedTableCell"" valign=top align=""center"">" & rsGetUserList("buss_unit") & "</td>" & _
            "<td class=""NestedTableCell"" valign=top >" & rsGetUserList("name") & "</td>" & _
            "<td class=""NestedTableCell"" valign=top >" & rsGetUserList("address") & "</td>" & _
            "<td class=""NestedTableCell"" valign=top >" & rsGetUserList("tel_no") & "</td>" & _
            "<td class=""NestedTableCell"" valign=top >" & rsGetUserList("email") & "</td>" & _
            "<td class=""NestedTableCell"" valign=top align=""center"">" & rsGetUserList("auth_yn") & "</td>" & _
            "<td class=""NestedTableCell"" valign=top >" & Format(rsGetUserList("created_on"), "yyyymmdd") & "</td>" & _
        "</tr>"
    
        rsGetUserList.MoveNext
    Loop
    
    PerformUserSearchActual = strHTMLHolder
    
    obj_ADOConn.Close
    
    Exit Function
PROC_ERROR:
    Err.Raise vbErrorNumbers.KT_PERFORMUSERSEARCH, "Keytax.clsSearch.PerformUserSearch", Err.Description
End Function

Public Function PerformUserSearch(ByVal strKeytaxUser As String, _
                          ByVal strComId As String, _
                          ByVal strSearchBy As String, _
                          ByVal strSearchFor As String, _
                          ByRef varMaxPages As Variant, _
                          ByRef varCurrentPage As Variant, _
                          ByVal strNextPrev As String, _
                          iRowsPerPage As Variant) As String

    On Error GoTo PROC_ERROR

    Dim strSQL As String
    Dim sModUser As String
    Dim strHTMLHolder As String
    Dim strIAm As String
    Dim strIWantTO As String
    Dim strCanYN As String
    
    Call modGeneral.dbConnect
                             
    Set rsGetUserList = New ADODB.Recordset
    'this sub will perform the intial search and return the Pageing recordset
    rsGetUserList.CursorType = adOpenStatic
    rsGetUserList.CursorLocation = adUseClient
    rsGetUserList.PageSize = iRowsPerPage
    rsGetUserList.CacheSize = iRowsPerPage

    strSQL = "exec dir_list_users " & strComId & ",'" & strSearchFor & "'," & strSearchBy
    rsGetUserList.Open strSQL, obj_ADOConn
    
    strSQL = "exec dir_allowed_function '" & strKeytaxUser & "', 'MOD_USER'"
    rsModUser.Open strSQL, obj_ADOConn
    
    sModUser = rsModUser("YN").Value
    
    'checks the users rights first
    If sModUser = "Y" Then
    
        'if the chaps can use this functionallity call the search function
        strHTMLHolder = strHTMLHolder & PerformUserSearchActual(strKeytaxUser, strComId, strSearchFor, strSearchBy, varCurrentPage, varMaxPages, strNextPrev)
    Else
        strHTMLHolder = strHTMLHolder & " " & _
        "<tr> " & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
            "<td class=""NestedTableCell"" width=""""    >No Information Found</td>" & _
        "</tr>"
    End If

    'return the constructed html
    PerformUserSearch = strHTMLHolder
    
    'disconect the recordset to free up resources
    rsGetUserList.ActiveConnection = Nothing
    
'    obj_ADOConn.Close
    Set modGeneral.obj_ADOConn = Nothing
    
    Exit Function
PROC_ERROR:
        Err.Raise vbErrorNumbers.KT_PERFORMUSERSEARCH, "Keytax.clsSearch.PerformUserSearch", Err.Description
End Function

Public Sub initUserSearch(ByVal strKeytaxUser As String, _
                          ByVal strComId As String, _
                          ByVal strSearchBy As String, _
                          ByVal strSearchFor As String, _
                          ByRef varMaxPages As Variant, _
                          ByRef varCurrentPage As Variant, _
                          ByVal strNextPrev As String, _
                          ByRef varAccess As Variant, _
                          Optional ByVal varRowsPerPage As Variant)
    
    Dim strSQL As String
    Dim sModUser As String
    Dim iRowsPerPage As Integer
    
    On Error GoTo PROC_ERROR
    
    Call modGeneral.dbConnect
                          
    'validate the rows per page variable from the web
    If CStr(varRowsPerPage) = "" Then
        iRowsPerPage = 20
    Else
        If CInt(varRowsPerPage) = 0 Then
            iRowsPerPage = 20
        Else
            If IsNumeric(varRowsPerPage) = True Then
                iRowsPerPage = CInt(varRowsPerPage)
            Else
                iRowsPerPage = 20
            End If
        End If
    End If
                        
    'this sub will perform the intial search and return the Pageing recordset
    rsGetUserList.CursorType = adOpenStatic
    rsGetUserList.CursorLocation = adUseClient
    rsGetUserList.PageSize = iRowsPerPage
    rsGetUserList.CacheSize = iRowsPerPage

    strSQL = "exec dir_list_users " & strComId & ",'" & strSearchFor & "'," & strSearchBy
    rsGetUserList.Open strSQL, obj_ADOConn
    
    strSQL = "exec dir_allowed_function '" & strKeytaxUser & "', 'MOD_USER'"
    rsModUser.Open strSQL, obj_ADOConn
    
    sModUser = rsModUser("YN").Value
    If UCase(sModUser) = "N" Then
        varAccess = "Access Denied"
    Else
        varAccess = ""
    End If
    
    varMaxPages = rsGetUserList.PageCount
    'close the recordset
    rsGetUserList.Close
    Set rsGetUserList = Nothing
    
    modGeneral.obj_ADOConn.Close
    Set modGeneral.obj_ADOConn = Nothing

Exit Sub
PROC_ERROR:
    Err.Raise vbErrorNumbers.KT_INIT_USERSEARCH, Err.Description & " - KT_Search.clsSearch.initUserSearch"

End Sub

'Public Function GetAllCompanies() As ADODB.Recordset
'    'THIS FUNCTION GETS ALL THE COMPANIES AND RETURNS THE RECORDSET OF THEM
'    On Error GoTo PROC_ERROR
'    Dim strSQL As String
'    Dim rsHolder As ADODB.Recordset
'
'    'init the recordset
'    Set rsHolder = New ADODB.Recordset
'
'    'connect to the database
'    Call modGeneral.dbConnect
'
'    rsHolder.CursorLocation = adUseClient
'    rsHolder.CursorType = adOpenStatic
'    modGeneral.obj_ADOConn.CursorLocation = adUseClient
'
'    'go fetch the info from the database
'    strSQL = "select * from dir_companies_vw order by contact_name"
'    Set rsHolder = modGeneral.obj_ADOConn.Execute(strSQL)
'
'    'disconnect the recordsets and deallocate the connection
'    Set rsHolder.ActiveConnection = Nothing
'    modGeneral.obj_ADOConn.Close
'    Set modGeneral.obj_ADOConn = Nothing
'
'    'return the info to the add bulletin page
'    Set GetAllCompanies = rsHolder
'
'    Exit Function
'PROC_ERROR:
'    Err.Raise vbErrorNumbers.KT_GET_ALL_COMPANIES, "KT_Search.clsSearch.GetAllCompanies", Err.Description
'End Function

Public Function GetBulletinTopic(ByRef strErrMSG As String) As Recordset

    Dim objConn As ADODB.Connection
    Dim objRs As ADODB.Recordset
    Dim objCmd As ADODB.Command
    Dim dllConnectionString
    Dim lngTotalRecords
    
   
    Set objConn = New ADODB.Connection
    Set dllConnectionString = CreateObject("Connect.ClsConnect")
                                                                        
    With objConn
        .CursorLocation = adUseClient
        .IsolationLevel = adXactBrowse
        .ConnectionString = dllConnectionString.ConnectionString
        .Open
    End With

    Set objRs = New ADODB.Recordset
        objRs.CursorLocation = adUseClient
        objRs.CursorType = adOpenStatic
        
    Set objCmd = New ADODB.Command
    objCmd.CommandText = "dir_get_topic"
    objCmd.CommandType = adCmdStoredProc
    objCmd.ActiveConnection = objConn
    
   
    Set objRs = objCmd.Execute
   
    Set objRs.ActiveConnection = Nothing
    
    Set GetBulletinTopic = objRs
    
   'clean up
   
    objConn.Close
    Set objConn = Nothing

   
   
End Function

Public Function GetBulletinDetails(ByVal BulletinID As String, ByRef strErrMSG As String) As Recordset

    Dim objConn As ADODB.Connection
    Dim objRs As ADODB.Recordset
    Dim objCmd As ADODB.Command
    Dim dllConnectionString
    Dim lngTotalRecords
    
   
    Set objConn = New ADODB.Connection
    Set dllConnectionString = CreateObject("Connect.ClsConnect")
                                                                        
    With objConn
        .CursorLocation = adUseClient
        .IsolationLevel = adXactBrowse
        .ConnectionString = dllConnectionString.ConnectionString
        .Open
    End With

    Set objRs = New ADODB.Recordset
        objRs.CursorLocation = adUseClient
        objRs.CursorType = adOpenStatic
        
    Set objCmd = New ADODB.Command
    objCmd.CommandText = "dir_get_bulletin_details"
    objCmd.CommandType = adCmdStoredProc
    objCmd.ActiveConnection = objConn
    
    objCmd.Parameters.Append objCmd.CreateParameter("nBulletinID", adDouble, adParamInput, , BulletinID)
    
    Set objRs = objCmd.Execute
    
    'clean up
    Set objRs.ActiveConnection = Nothing
    
        
    Set GetBulletinDetails = objRs
    
    'Clean up
    objConn.Close
    Set objConn = Nothing
   
End Function

Public Function UpdateBulletin(ByVal nBulletinID As String, _
                               ByVal strBulletinTopic As String, _
                               ByVal strBulletinText As String, _
                               ByVal strStartDate As String, _
                               ByVal strEndDate As String) As Boolean
                               
On Error GoTo PROC_ERROR

    Dim bResult As Boolean
    Dim objConn As ADODB.Connection
    Dim objRs As ADODB.Recordset
    Dim objCmd As ADODB.Command
    Dim dllConnectionString
    Dim lngTotalRecords
    Dim lngRecordsAffected As Long
    Dim strErrMSG
    Dim strStartDateTime
    Dim strEndDateTime
    Dim dtLastModified
   
    Set objConn = New ADODB.Connection
    Set dllConnectionString = CreateObject("Connect.ClsConnect")
                                                                        
    With objConn
        .CursorLocation = adUseClient
        .IsolationLevel = adXactBrowse
        .ConnectionString = dllConnectionString.ConnectionString
        .Open
    End With

    Set objCmd = New ADODB.Command
    objCmd.CommandText = "dir_update_bulletin"
    objCmd.CommandType = adCmdStoredProc
    objCmd.ActiveConnection = objConn
    
    strStartDateTime = CDate(Format(strStartDate, "dd/mm/yyyy hh:mm:ss"))
    
    strEndDateTime = CDate(Format(strEndDate, "dd/mm/yy hh:mm:ss"))
    
    dtLastModified = CDate(Format(Now, "dd/mm/yy hh:mm:ss"))
    
    objCmd.Parameters.Append objCmd.CreateParameter("@BulletinID", adDouble, adParamInput, , nBulletinID)
    objCmd.Parameters.Append objCmd.CreateParameter("@BulletinTopic", adVarChar, adParamInput, 200, strBulletinTopic)
    objCmd.Parameters.Append objCmd.CreateParameter("@BulletinText", adVarChar, adParamInput, 6000, strBulletinText)
    objCmd.Parameters.Append objCmd.CreateParameter("@StartDate", adDBTimeStamp, adParamInput, , strStartDate)
    objCmd.Parameters.Append objCmd.CreateParameter("@EndTime", adDBTimeStamp, adParamInput, , strEndDate)
    objCmd.Parameters.Append objCmd.CreateParameter("@dtLastModified", adDBTimeStamp, adParamInput, , dtLastModified)
    
    objCmd.Execute lngRecordsAffected
    
    If lngRecordsAffected >= 1 Then
        bResult = True
    End If
 

PROC_EXIT:
  objConn.Close
  Set objConn = Nothing
  UpdateBulletin = bResult
  Exit Function
  
PROC_ERROR:
  bResult = False
  MsgBox Err.Description, _
         vbOKOnly + vbExclamation, _
         Err.Source
  GoTo PROC_EXIT
    
End Function

Public Function GetArchiveBulletins() As String
    'this function will return the actual bulletin
    
    On Error GoTo PROC_ERROR
    
    Dim cmdGet As ADODB.Command
    Dim rsBulletin As ADODB.Recordset
    Dim strHTML As String
    
    Set rsBulletin = New ADODB.Recordset
    Set cmdGet = New ADODB.Command
    
    'connect to the database
    Call modGeneral.dbConnect

    'set up the command
    With cmdGet
        .ActiveConnection = modGeneral.obj_ADOConn
        .CommandText = "dir_get_archive_details"
        .CommandType = adCmdStoredProc
        
        Set rsBulletin = .Execute
    End With

    'check the rs
    If rsBulletin.EOF = True And rsBulletin.BOF = True Then
        'there were no records returned for this com_id, this should !*NEVER*! happen
        Err.Raise vbErrorNumbers.KT_GET_BULLETINS, "KT_Search.clsBulletin.GetBulletins"
    End If

    'generate the HTML table
    Do While rsBulletin.EOF = False
        strHTML = strHTML & "<tr> " & _
                  "<td class=""FormHeadingCell"">" & CStr(rsBulletin("bulletin_topic")) & "</td>" & _
                  "</tr>" & _
                  "<tr>" & _
                  "<td class=""FormLeftCell"">" & CStr(rsBulletin("bulletin_text")) & "</td>" & _
                  "</tr>" & _
                  "</TABLE><br><TABLE class=""FormTable"" cellpadding=2 cellspacing=1 align=center style=""width:90%"">"
        rsBulletin.MoveNext
    Loop
    
    'deallocate objects
    Set cmdGet = Nothing
    rsBulletin.Close
    Set rsBulletin = Nothing
    modGeneral.obj_ADOConn.Close
    Set modGeneral.obj_ADOConn = Nothing

    GetArchiveBulletins = strHTML
    Exit Function
PROC_ERROR:
    Err.Raise vbErrorNumbers.KT_GET_BULLETINS, "KT_Search.clsBulletin.GetBulletins", Err.Description
End Function

Public Function GetDirectiveDetails(ByVal nReqSeqNumber) As String

On Error GoTo PROC_ERROR

Dim objConn As ADODB.Connection
Dim rsDirectiveNumber As ADODB.Recordset
Dim objComm As ADODB.Command
Dim dllConnectionString As Object
Dim strConnection As String
Dim nDirectiveNumber As Long

    Set objConn = New ADODB.Connection
    Set dllConnectionString = CreateObject("Connect.ClsConnect")
    strConnection = dllConnectionString.ConnectionString
                                                                        
    With objConn
        .CursorLocation = adUseClient
        .IsolationLevel = adXactBrowse
        .ConnectionString = strConnection
        .Open
    End With
        
    Set objComm = New ADODB.Command
    With objComm
        .CommandText = "dir_get_directive_number"
        .CommandType = adCmdStoredProc
        Set .ActiveConnection = objConn
        .Parameters.Append .CreateParameter("req_seq_num", adDouble, adParamInput, , nReqSeqNumber)
    End With
    
    Set rsDirectiveNumber = New ADODB.Recordset
    With rsDirectiveNumber
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
    End With
    
    Set rsDirectiveNumber = objComm.Execute
    
    nDirectiveNumber = rsDirectiveNumber.Fields("directive_number").Value
    
    
    'Clean Up
    Set rsDirectiveNumber.ActiveConnection = Nothing
    rsDirectiveNumber.Close
    Set rsDirectiveNumber = Nothing
    Set objComm = Nothing
    objConn.Close
    Set objConn = Nothing
    
    GetDirectiveDetails = CStr(nDirectiveNumber)
   Exit Function
PROC_ERROR:
        Err.Raise vbErrorNumbers.KT_GET_DIRECTIVE_DETAILS, "Keytax.clsSearch.GetDirectiveDetails", Err.Description
End Function




